package flex.samples.product;

import java.util.ArrayList;
import java.util.List;
import java.sql.*;

import flex.samples.ConnectionHelper;
import flex.samples.DAOException;

public class ProductService {

	public List getProducts() throws DAOException {

		List list = new ArrayList();
		Connection c = null;

		try {
			c = ConnectionHelper.getConnection();
			Statement s = c.createStatement();
			ResultSet rs = s.executeQuery("SELECT * FROM product ORDER BY name");
			while (rs.next()) {
				list.add(new Product(rs.getInt("product_id"),
						rs.getString("name"),
						rs.getString("description"),
						rs.getString("image"), 
						rs.getString("category"), 
						rs.getDouble("price"),
						rs.getInt("qty_in_stock")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;

	}

	public List getProductsByName(String name) throws DAOException {
		
		List list = new ArrayList();
		Connection c = null;
		
		try {
			c = ConnectionHelper.getConnection();
			PreparedStatement ps = c.prepareStatement("SELECT * FROM product WHERE UPPER(name) LIKE ? ORDER BY name");
			ps.setString(1, "%" + name.toUpperCase() + "%");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				list.add(new Product(rs.getInt("product_id"),
						rs.getString("name"),
						rs.getString("description"),
						rs.getString("image"), 
						rs.getString("category"), 
						rs.getDouble("price"),
						rs.getInt("qty_in_stock")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;
		
	}
	
	public Product getProduct(int productId) throws DAOException {

		Product product = new Product();
		Connection c = null;

		try {
			c = ConnectionHelper.getConnection();
			PreparedStatement ps = c.prepareStatement("SELECT * FROM product WHERE product_id=?");
			ps.setInt(1, productId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				product = new Product();
				product.setProductId(rs.getInt("product_id"));
				product.setName(rs.getString("name"));
				product.setDescription(rs.getString("description"));
				product.setImage(rs.getString("image")); 
				product.setCategory(rs.getString("category")); 
				product.setPrice(rs.getDouble("price"));
				product.setQtyInStock(rs.getInt("qty_in_stock"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new DAOException(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return product;
	}

	public Product create(Product product) throws DAOException {
		
		Connection c = null;
		PreparedStatement ps = null;
		try {
			c = ConnectionHelper.getConnection();
			ps = c.prepareStatement("INSERT INTO product (name, description, image, category, price, qty_in_stock) VALUES (?, ?, ?, ?, ?, ?)");
			ps.setString(1, product.getName());
			ps.setString(2, product.getDescription());
			ps.setString(3, product.getImage());
			ps.setString(4, product.getCategory());
			ps.setDouble(5, product.getPrice());
			ps.setInt(6, product.getQtyInStock());
			ps.executeUpdate();
			Statement s = c.createStatement();
			// HSQLDB Syntax to get the identity (company_id) of inserted row
			ResultSet rs = s.executeQuery("CALL IDENTITY()");
			// MySQL Syntax to get the identity (product_id) of inserted row
			// ResultSet rs = s.executeQuery("SELECT LAST_INSERT_ID()");
			rs.next();
            // Update the id in the returned object. This is important as this value must get returned to the client.
			product.setProductId(rs.getInt(1));
		} catch (Exception e) {
			e.printStackTrace();
			throw new DAOException(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return product;
	}

	public boolean update(Product product) throws DAOException {

		Connection c = null;

		try {
			c = ConnectionHelper.getConnection();
			PreparedStatement ps = c.prepareStatement("UPDATE product SET name=?, description=?, image=?, category=?, price=?, qty_in_stock=? WHERE product_id=?");
			ps.setString(1, product.getName());
			ps.setString(2, product.getDescription());
			ps.setString(3, product.getImage());
			ps.setString(4, product.getCategory());
			ps.setDouble(5, product.getPrice());
			ps.setInt(6, product.getQtyInStock());
			ps.setInt(7, product.getProductId());
			return (ps.executeUpdate() == 1);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOException(e);
		} finally {
			ConnectionHelper.close(c);
		}

	}

	public boolean remove(Product product) throws DAOException {
		
		Connection c = null;
		
		try {
			c = ConnectionHelper.getConnection();
			PreparedStatement ps = c.prepareStatement("DELETE FROM product WHERE product_id=?");
			ps.setInt(1, product.getProductId());
			int count = ps.executeUpdate();
			return (count == 1);
		} catch (Exception e) {
			e.printStackTrace();
			throw new DAOException(e);
		} finally {
			ConnectionHelper.close(c);
		}
	}

	public boolean delete(Product product) throws DAOException {
		return remove(product);
	}

}